import random
import time
from time import sleep
from analysis import save
import pymysql
import sqlite3

'''
里面有一些方法和装饰器
execute_sql_query:链接数据库进行sql语句的操作
extract_first(list): 如果传入的列表非空，就取第一个值，一般结合xpath使用
@sleep_inclass(sleep_mix = 6,sleep_max = 10)：用来sleep的装饰器，给类中的方法使用
@sleeper(sleep_mix = 6,sleep_max = 10)：用来sleep的装饰器，给普通方法使用
@try_except_inclass()：就是try，except，给类中的方法使用
@try_except()就是try，except，给普通方法使用
@running_time_inclass(print_thing)：统计程序运行时长，给类中的方法使用
@running_time(print_thing)：统计程序运行时长，给普通方法使用
header_trans(header_filename):header直接复制粘贴到txt中，转换成dict

装饰器说明：
    最好都return被装饰函数的执行结果
    不然碰到有renturn值的函数，被装饰后return的结果就变成None了
    可以先接一下函数的值，再return，这样函数前后都可以随便加东西
    
    装饰器执行顺序：outer——>inner——>func
    多层装饰器执行顺序：under outer——>upper outer——>upper inner——>under unner——>func
                        ——相当于把上面装饰器的内容插在了下面装饰器的中间
                        
    带参装饰器不是标准的装饰器
        inner外面的代码可能在程序开始时运行，只有inner内的代码是实际装饰被装饰函数的。
'''

def check_response(func):
    def inner(self, response, *args, **kwargs):
        if response.status == 200:
            return func(self, response, *args, **kwargs)
        else:
            print_save_txt('请求失败{}'.format(response))
    return inner

#装饰器 try_except,用于普通函数中
def try_except(func):
    def inner(*args, **kwargs):
        try:
            export = func(*args, **kwargs)
            return export
        except Exception as e:
            print('这个方法运行时出错了：{} '.format(func.__name__))
            print(e)
    return inner

#传入sql语句即可进行相关操作
@try_except
def execute_sql_query(db,sql_list,base = 'mysql',charset = "utf8",mod = None):
    '''
    db: 如果base='mysql'，传入database的名字即可；如果base == 'sqlite'，db传入文件名如：'test.db'
    sql_list:[sql语句,sql语句,sql语句]

    要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；
    连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。
    Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只需要提供符合Python标准的数据库驱动即可。
    由于SQLite的驱动内置在Python标准库中，所以我们可以直接来操作SQLite数据库

    写入或者读取数据时一行是一个元组，多行是列表中套着一堆元组
    插入，写入数据一定要有conn.commit()这句来提交事务，要不然不能真正的插入数据

    插入多行有个例子：
    values=[]
    for i in range(20):
        values.append((i,'hi rollen'+str(i)))
    cur.executemany('insert into test values(%s,%s)',values)
    '''
    #进行数据库连接
    if base == 'mysql':
        conn = pymysql.Connect(host="127.0.0.1", port=3306, user="root", password="", db=db, charset=charset)
    if base == 'sqlite':
        conn = sqlite3.connect('test.db')
    cursor = conn.cursor() #打开游标

    data_list = []
    for sql in sql_list:
        if mod == 'query':#用于查询数据库
            cursor.execute(sql)  # 执行sql语句
            data = cursor.fetchall() #相当于从数据库取数据，但是取完就没有了，再下一行继续 cursor.fetchall()，取到的就只是空列表。他和变量不一样，不能重复查询，推荐将数据取出来之后，放到一个变量里，再进行处理。
            data_list.append(data)
        else:#用于insert，update之类的
            if mod == 'many':
                cursor.executemany(sql)  # 执行sql语句
            else:
                cursor.execute(sql)  # 执行sql语句
            conn.commit() # 提交事务，不然insert，update之类的操作就无效了

    cursor.close() #关闭游标
    conn.close() #断开连接
    return data_list

#如果是非空就提取列表的第一个元素，非空判断很重要，不然遇到一个空值就卡死了
def extract_first(data):
    #如果列表是空的话data[0]会报错，所以先判定一下是否为空再提
    if data:
        return data[0]
    return ""

#带参装饰器，用于class下的方法中，控制睡眠时长,sleep_time为int或
def sleep_inclass(sleep_time = None,sleep_mix = None,sleep_max = None):
    def outer(func):
        def inner(self, *args, **kwargs):
            export =  func(self, *args, **kwargs)
            if sleep_time is not None:
                sleep(sleep_time)
            elif sleep_mix is not None and sleep_max is not None :
                sleep(random.uniform(sleep_mix, sleep_max))
            else:
                print('装饰器参数填写错误：sleep_inclass(sleep_time = None,sleep_mix = None,sleep_max = None)')
            return export
        return inner
    return outer

#带参装饰器，用于普通方法中，控制睡眠时长,参数均为int，可以固定时长也可以随机时长
def sleeper(sleep_time = None,sleep_mix = None,sleep_max = None):
    def outer(func):
        def inner(*args, **kwargs):
            export = func(*args, **kwargs)
            if sleep_time is not None:
                sleep(sleep_time)
            elif sleep_mix is not None and sleep_max is not None:
                sleep(random.uniform(sleep_mix, sleep_max))
            else:
                print('装饰器参数填写错误：sleep_inclass(sleep_time = None,sleep_mix = None,sleep_max = None)')
            return export
        return inner
    return outer

#装饰器 try_except,用于class下的方法中
def try_except_inclass(func):
    def inner(self, *args, **kwargs):
        try:
            export = func(self, *args, **kwargs)
            return export
        except Exception as e:
            print('这个方法运行时出错了！！！：{} '.format(func.__name__))
            print(e)
    return inner

#带参装饰器，用于class下的方法中,用于统计程序运行时长
#print_thing中有{:.2f}控制小数点保留位数
def running_time_inclass(print_thing,save_name = None):
    def outer(func):
        def inner(self, *args, **kwargs):
            time_start = time.time()
            export = func(self, *args, **kwargs)
            time_end = time.time()
            time_spend = time_end - time_start

            print(print_thing.format(time_spend))
            if save_name is not None:
                save.save_txt(save_name,print_thing.format(time_spend),mod='a')
            return export
        return inner
    return outer

#带参装饰器，用于普通方法中，用于统计程序运行时长
#print_thing中有{:.2f}控制小数点保留位数
def running_time(print_thing, save_name=None):
    def outer(func):
        def inner(*args, **kwargs):
            time_start = time.time()
            export = func(*args, **kwargs)
            time_end = time.time()
            time_spend = time_end - time_start

            print(print_thing.format(time_spend))
            if save_name is not None:
                save.save_txt(save_name, print_thing.format(time_spend), mod='a')
            return export
        return inner
    return outer

# header直接复制粘贴到txt中，转换成dict
def header_trans(header_filename,prin = False):
    '''header直接复制粘贴到txt中，header_filename（如：'headers.txt'）'''
    with open(header_filename, "r") as f:  # 打开文件
        headers = f.read()  # 读取文件
    #按\n分割成list
    trans_list = headers.split('\n')

    #将list转换成dict
    trans_dict = {}
    for i in trans_list:
        if ': ' in i:
            item = i.split(': ')
        elif ':' in i:
            item = i.split(':')
        else:
            print("未找到分隔符':'or': '")
        trans_dict[item[0]]=item[1]
    if prin == True:
        print(trans_dict)

    return trans_dict


#免费代理网址列表
daili_webset_dict = {'快代理':'https://www.kuaidaili.com/free/',
                '66免费代理':'http://www.66ip.cn/',
                '89免费代理':'https://www.89ip.cn/',
                'mimvp':'https://proxy.mimvp.com/freesecret.php',
                '云代理':'http://www.ip3366.net/',
                'seofangfa':'https://seofangfa.com/proxy/',
                '鲲鹏':'http://www.site-digger.com/html/articles/20110516/proxieslist.html',
                '尼玛代理':'http://www.nimadaili.com/',
                '芝麻代理':'https://zhimahttp.com/?utm-source=bdtg&utm-keyword=?DL201198',
                }

#pc端User-Agent列表
user_agent_list_pc = [
        # 各种PC端
        # Opera
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36 OPR/26.0.1656.60",
        "Opera/8.0 (Windows NT 5.1; U; en)",
        "Mozilla/5.0 (Windows NT 5.1; U; en; rv:1.8.1) Gecko/20061208 Firefox/2.0.0 Opera 9.50",
        "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; en) Opera 9.50",
        # Firefox
        "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:34.0) Gecko/20100101 Firefox/34.0",
        "Mozilla/5.0 (X11; U; Linux x86_64; zh-CN; rv:1.9.2.10) Gecko/20100922 Ubuntu/10.10 (maverick) Firefox/3.6.10",
        # Safari
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2",
        # chrome
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.71 Safari/537.36",
        "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11",
        "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.133 Safari/534.16",
        # 360
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36",
        "Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko",
        # 淘宝浏览器
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.11 (KHTML, like Gecko) Chrome/20.0.1132.11 TaoBrowser/2.0 Safari/536.11",
        # 猎豹浏览器
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.71 Safari/537.1 LBBROWSER",
        "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; LBBROWSER)",
        "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; QQDownload 732; .NET4.0C; .NET4.0E; LBBROWSER)",
        # QQ浏览器
        "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; QQBrowser/7.0.3698.400)",
        "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; QQDownload 732; .NET4.0C; .NET4.0E)",
        # sogou浏览器
        "Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.84 Safari/535.11 SE 2.X MetaSr 1.0",
        "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; SV1; QQDownload 732; .NET4.0C; .NET4.0E; SE 2.X MetaSr 1.0)",
        # maxthon浏览器
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Maxthon/4.4.3.4000 Chrome/30.0.1599.101 Safari/537.36",
        # UC浏览器
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 UBrowser/4.0.3214.0 Safari/537.36",
        # 一部分 PC端的
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/22.0.1207.1 Safari/537.1",
        "Mozilla/5.0 (X11; CrOS i686 2268.111.0) AppleWebKit/536.11 (KHTML, like Gecko) Chrome/20.0.1132.57 Safari/536.11",
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.6 (KHTML, like Gecko) Chrome/20.0.1092.0 Safari/536.6",
        "Mozilla/5.0 (Windows NT 6.2) AppleWebKit/536.6 (KHTML, like Gecko) Chrome/20.0.1090.0 Safari/536.6",
        "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/19.77.34.5 Safari/537.1",
        "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.9 Safari/536.5",
        "Mozilla/5.0 (Windows NT 6.0) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.36 Safari/536.5",
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1063.0 Safari/536.3",
        "Mozilla/5.0 (Windows NT 5.1) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1063.0 Safari/536.3",
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_0) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1063.0 Safari/536.3",
        "Mozilla/5.0 (Windows NT 6.2) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1062.0 Safari/536.3",
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1062.0 Safari/536.3",
        "Mozilla/5.0 (Windows NT 6.2) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1061.1 Safari/536.3",
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1061.1 Safari/536.3",
        "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1061.1 Safari/536.3",
        "Mozilla/5.0 (Windows NT 6.2) AppleWebKit/536.3 (KHTML, like Gecko) Chrome/19.0.1061.0 Safari/536.3",
        "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.24 (KHTML, like Gecko) Chrome/19.0.1055.1 Safari/535.24",
        "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/535.24 (KHTML, like Gecko) Chrome/19.0.1055.1 Safari/535.24"
    ]

#手机端User-Agent列表
user_agent_list_phone = [
        # IPhone
        "Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
        # IPod
        "Mozilla/5.0 (iPod; U; CPU iPhone OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
        # IPAD
        "Mozilla/5.0 (iPad; U; CPU OS 4_2_1 like Mac OS X; zh-cn) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8C148 Safari/6533.18.5",
        "Mozilla/5.0 (iPad; U; CPU OS 4_3_3 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8J2 Safari/6533.18.5",
        # Android
        "Mozilla/5.0 (Linux; U; Android 2.2.1; zh-cn; HTC_Wildfire_A3333 Build/FRG83D) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
        "Mozilla/5.0 (Linux; U; Android 2.3.7; en-us; Nexus One Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
        # QQ浏览器 Android版本
        "MQQBrowser/26 Mozilla/5.0 (Linux; U; Android 2.3.7; zh-cn; MB200 Build/GRJ22; CyanogenMod-7) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
        # Android Opera Mobile
        "Opera/9.80 (Android 2.3.4; Linux; Opera Mobi/build-1107180945; U; en-GB) Presto/2.8.149 Version/11.10",
        # Android Pad Moto Xoom
        "Mozilla/5.0 (Linux; U; Android 3.0; en-us; Xoom Build/HRI39) AppleWebKit/534.13 (KHTML, like Gecko) Version/4.0 Safari/534.13",
        # BlackBerry
        "Mozilla/5.0 (BlackBerry; U; BlackBerry 9800; en) AppleWebKit/534.1+ (KHTML, like Gecko) Version/6.0.0.337 Mobile Safari/534.1+",
        # WebOS HP Touchpad
        "Mozilla/5.0 (hp-tablet; Linux; hpwOS/3.0.0; U; en-US) AppleWebKit/534.6 (KHTML, like Gecko) wOSBrowser/233.70 Safari/534.6 TouchPad/1.0",
        # Nokia N97
        "Mozilla/5.0 (SymbianOS/9.4; Series60/5.0 NokiaN97-1/20.0.019; Profile/MIDP-2.1 Configuration/CLDC-1.1) AppleWebKit/525 (KHTML, like Gecko) BrowserNG/7.1.18124",
        # Windows Phone Mango
        "Mozilla/5.0 (compatible; MSIE 9.0; Windows Phone OS 7.5; Trident/5.0; IEMobile/9.0; HTC; Titan)",
        # UC浏览器
        "UCWEB7.0.2.37/28/999",
        "NOKIA5700/ UCWEB7.0.2.37/28/999",
        # UCOpenwave
        "Openwave/ UCWEB7.0.2.37/28/999",
        # UC Opera
        "Mozilla/4.0 (compatible; MSIE 6.0; ) Opera/UCWEB7.0.2.37/28/999",
    ]

if __name__ == '__main__':

    print(execute_sql_query.__name__)

